﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DTO;
using DAO;

namespace DAO
{
    public class TongKetHocKyDAO
    {

        public static int LoadDuLieuXuong(DataTable tb)
        {
            string command;
            string hocKy; string lop; int siSo; int soLuongDat; float tyLe;

            DataTable tb2 = tb;
            int irow = tb.Rows.Count;
            int icolumn = tb.Columns.Count;

            int flag = 1;
            for (int i = 0; i < irow; i++)
            {

                hocKy = tb.Rows[i][0].ToString();
                lop = tb.Rows[i][1].ToString();
                siSo = int.Parse(tb.Rows[i][2].ToString());
                soLuongDat = int.Parse(tb.Rows[i][3].ToString());
                tyLe = float.Parse(tb.Rows[i][4].ToString());

                command = " insert into TongKetHocKy( hocKy, maLop, siSo, soLuongDat, tyLe ) values('" + hocKy + "','" + lop + "'," + siSo.ToString() + "," + soLuongDat.ToString() + "," + tyLe.ToString() + ")";

                flag = DaTaProvider.ExcecuteNonQuery(command);
                if (flag == 0)
                    return 0;
            }
            return 1;
        }
        #region them du lieu
        public static bool Insert( string hocKy, string lop, int siSo, int soLuongDat, float tyLe )
        {
            string command = " insert into TongKetHocKy( hocKy, maLop, siSo, soLuongDat, tyLe ) values('" + hocKy + "','" + lop + "'," + siSo.ToString() + "," + soLuongDat.ToString() +  "," + tyLe.ToString() +  ")" ;

          //  try
            {
                DaTaProvider.ExcecuteNonQuery(command);
            }
            //catch (Exception ex)
            {
                //throw ex;
                return false;
            }

            return true;

        }
        #endregion

        public static DataTable TongKetHocKy( string hocKy )
        {

            #region  lay du lieu len de xy ly
            string command = "select * from TongKetMonHoc"
                                + " where TongKetMonHoc.hocKy = '" + hocKy +  "'";
            DataTable tbDuLieu = new DataTable();
            try
            {
               tbDuLieu =  DaTaProvider.excecuteQuery(command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            #endregion

            DataTable tbKetQua = new DataTable();

            #region  lay thuoc tinh tu bang TongKetHocKy
            DataTable tbTongKet = new DataTable();
            command = "select * from TongKetHocKy where 1 = 2";
            try
            {
               tbTongKet =  DaTaProvider.excecuteQuery(command);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            tbKetQua = tbTongKet.Clone();
            tbKetQua.Columns[0].ColumnName = "Học Kỳ";
            tbKetQua.Columns[1].ColumnName = "Mã Lớp";
            tbKetQua.Columns[2].ColumnName = "Sĩ Số";
            tbKetQua.Columns[3].ColumnName = "Số Lượng đạt";
            tbKetQua.Columns[4].ColumnName = "Tỷ Lệ";

            #endregion

            
            // khoi tao flag 
            int irow = tbDuLieu.Rows.Count;
            int[] flag = new int[irow];
            for (int i = 0; i < irow; i++)
                flag[i] = 1;

            //xu ly
           
            int soLuongDatMin = 0;
            float tyLeMin = 0;
            for (int i = 0; i < irow; i++)
            {
                
                if( flag[i] == 1 )
                {
                    DataRow r = tbKetQua.NewRow();
                    flag[i] = 0;
                    string maLop = tbDuLieu.Rows[i][2].ToString();
                    soLuongDatMin = int.Parse(tbDuLieu.Rows[i][4].ToString());
                    tyLeMin = float.Parse(tbDuLieu.Rows[i][5].ToString());

                    for (int j = i + 1; j < irow; j++)
                    {
                        if (flag[j] == 1 && string.Compare(tbDuLieu.Rows[j][2].ToString(), maLop) == 0)
                        {
                            flag[j] = 0;

                            int soLuongDat = int.Parse( tbDuLieu.Rows[j][4].ToString() );
                            if (soLuongDat < soLuongDatMin)
                            {
                                soLuongDatMin = soLuongDat;
                            }

                            float tyLe = float.Parse(tbDuLieu.Rows[j][5].ToString());
                            if (tyLe < tyLeMin)
                            {
                                tyLeMin = tyLe;
                            }
                        }
                    }

                    r[0] = hocKy;
                    r[1] = maLop;
                    r[2] = tbDuLieu.Rows[i][3];
                    r[3] = soLuongDatMin;
                    r[4] = tyLeMin;

                    tbKetQua.Rows.Add( r );

                    //dua du lieu vao sql server
                   // Insert( hocKy, maLop, int.Parse(r[2].ToString()) , int.Parse(r[3].ToString()) , float.Parse(r[4].ToString()));
                }
            }
            return tbKetQua;

        }

    }
}
